'''
服务器逻辑主体
处理数据同时广播数据和将数据与mysql进行同步

'''

from socket import *
import _thread,time
import pymysql
from pymysql.cursors import DictCursor

#init
LISTEN = True
SERVER_IP = "127.0.0.1"
SERVER_PORT = 2333
SERVER_MAX_USER = 10
BUFFER = 1024
SERVER_ADDR = (SERVER_IP,SERVER_PORT)
server = socket(AF_INET,SOCK_STREAM)
server.bind(SERVER_ADDR)#服务器创建
server.listen(SERVER_MAX_USER)#开启监听
client_list = []
DB_IP = "81.69.177.243"
DB_USER = "root"
DB_PASSWORD = "zxc123aas"
DATA_KEY_CODE = "#"



class DB:
    def __init__(self) -> None:
        #连接mysql
        self.db = pymysql.connect(host=DB_IP,port=3306,user=DB_USER,password=DB_PASSWORD,charset="utf8",database="game_un",autocommit=True)
        #创建游标
        self.cursor = self.db.cursor(DictCursor)
        print("mysql数据库已初始化。")
    #查询
    def select(self,conm,table_name,where=None):
        #构造sql语句
        if where == None:
            sql_code = "SELECT {} FROM {}".format(conm,table_name)
        else:
            sql_code = "SELECT {} FROM {} WHERE {}".format(conm,table_name,where)
        #执行sql语句
        self.cursor.execute(sql_code)
        #返回结果
        result = self.cursor.fetchall()
        return result
    #插入
    def insert(self,conms,table_name,values):
        #将list转为text
        i = len(conms)
        n = 0
        conms_text = ""
        for conm in conms:
            if n == i-1:
                conms_text += conm
            else:
                conms_text += conm + ","
            n += 1
        i = len(values)
        values_text = ""
        n = 0
        for value in values:
            if n == i-1:
                values_text += "'"+value+"'"
            else:
                values_text += "'"+value+"'"+","
            n += 1
        sql_code = """INSERT INTO {}({}) VALUES({})""".format(table_name,conms_text,values_text)
        self.cursor.execute(sql_code)
    #更新
    def update(self,conm,conm_value,table_name,where):
        #构造sql语句
        sql_code = "UPDATE {} SET {}='{}' WHERE {}".format(table_name,conm,conm_value,where)
        #执行sql语句
        self.cursor.execute(sql_code)

#初始化db
mysql_db = DB()


def getClient(socket_connection,client_info):#接受客户端
    '''
    param socket_connection:连接到服务器的socket对象；client_info:连接到服务器的客户端信息ip，port
    主要处理客户端数据和对应逻辑。
    
    '''
    client_list.append(socket_connection)
    print("{}:{}已连接到服务器！".format(client_info[0],client_info[1]))
    socket_connection.send("connected!".encode())#发送反馈
    PLAYER_ONLINE = True
    #玩家数据处理循环
    while PLAYER_ONLINE:
        temp = socket_connection.recv(BUFFER).decode()
        #处理data
        temp = str(temp).split(DATA_KEY_CODE)
        data = {"username":temp[0],"movement":temp[1],"contact":temp[2]}
        if data["movement"] == "data":
            data = mysql_db.select()
        #发送数据
        socket_connection.send(data.encode())
            


#接受连接
while LISTEN:
    socket_connection, client_info = server.accept()
    _thread.start_new_thread(getClient,(socket_connection,client_info))#启动多线程
